package com.bj58.groupbuy.action.db;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import com.bj58.groupbuy.action.model.DBHostPwd;
import com.bj58.groupbuy.action.model.DBPriviledge;
import com.bj58.groupbuy.action.model.Role;
import com.bj58.groupbuy.action.model.RoleOperPriviledge;
import com.bj58.groupbuy.action.model.RolePriviledge;
import com.bj58.groupbuy.action.model.User;
import com.bj58.groupbuy.action.model.UserOperPriviledge;
import com.bj58.groupbuy.action.model.UserPriviledge;
import com.bj58.groupbuy.action.model.UserRole;
import com.bj58.groupbuy.action.util.MD5;
import com.bj58.groupbuy.util.DateUtil;

public class UserPriviledgeDao extends CommonDao {
	public static HashSet<String> excludeDBSet = new HashSet<String>();
	static {
		String[] excludeDB = { "information_schema", "mysql","performance_schema", "BH_RSI_Repository", "sys_infobright" };
		Collections.addAll(excludeDBSet, excludeDB);
		// System.out.println(excludeDBSet);
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public <T> boolean updateByVo(Statement st, List<String> cols,String table, Class<T> clazz, T vo, String condition)throws Exception {
		Method[] mm = clazz.getMethods();
		Map<String, Method> methodMap = new HashMap<String, Method>();
		for (Method m : mm) {
			methodMap.put(m.getName(), m);
		}
		if (null == cols || cols.isEmpty()) {
			cols = new ArrayList<String>();
			Field[] fs = clazz.getDeclaredFields();
			for (Field field : fs) {
				cols.add(field.getName());
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update " + table + " set ");
		for (int i = 0; i < cols.size(); i++) {

			String methodName = "get"+ cols.get(i).substring(0, 1).toUpperCase()+ cols.get(i).substring(1);
			Object value = methodMap.get(methodName).invoke(vo);
			if (value == null) {
				if ((i + 1) == cols.size())
					sql.append(" where ");
				continue;
			}
			sql.append(cols.get(i));
			sql.append("='" + value + "'");
			if ((i + 1) < cols.size()) {
				sql.append(",");
			} else {
				sql.append(" where ");
			}
		}
		sql.append(condition);
		st.executeUpdate(sql.toString());
		return true;
	}

	public <T> boolean insertRecode(Statement st, List<String> cols,String table, Class<T> clazz, T vo, int type) throws Exception {
		ResultSet rs = null;
		try {
			HashMap<String, Field> fieldMap = new HashMap<String, Field>();
			Field[] fs = clazz.getDeclaredFields();
			for (Field field : fs) {
				fieldMap.put(field.getName(), field);
			}
			if (null == cols || cols.isEmpty()) {
				cols = new ArrayList<String>();
				for (Field field : fs) {
					cols.add(field.getName());
				}
			}
			StringBuffer columns = new StringBuffer();
			StringBuffer v = new StringBuffer();
			columns.append(" (");
			v.append(" values (");
			Method[] mm = clazz.getMethods();
			Map<String, Method> methodMap = new HashMap<String, Method>();
			for (Method m : mm) {
				methodMap.put(m.getName(), m);
			}
			for (int i = 0; i < cols.size(); i++) {
				String methodName = "get"
						+ cols.get(i).substring(0, 1).toUpperCase()
						+ cols.get(i).substring(1);
				Object value = methodMap.get(methodName).invoke(vo);
				if (value == null && (i + 1) < cols.size()) {
					continue;
				} else {
					if (fieldMap.get(cols.get(i)).getType() == Date.class)
						value = DateUtil.getStringDate((Date) value,
								"yyyy-MM-dd HH:mm:ss");
					columns.append(cols.get(i));
					v.append("'" + value + "'");
				}
				if ((i + 1) < cols.size()) {
					columns.append(",");
					v.append(",");
				} else {
					columns.append(")");
					v.append(")");
				}
			}
			StringBuffer sql = new StringBuffer();
			sql.append("insert into " + table);
			sql.append(columns);
			sql.append(v);
			// System.out.println(sql);
//			st.executeUpdate(sql.toString());
			st.execute(sql.toString());
		} finally {
			if (rs != null)
				rs.close();
		}
		return true;
	}

	public boolean saveRoleByfunctions(String functions, Role r, String opers, int type) {
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			String sql = "select max(id) from role";
			rs = st.executeQuery(sql);
			int roleid = 0;
			if (rs.next())
				roleid = rs.getInt(1) + 1;
			r.setId(roleid);
			boolean result = insertRecode(st, null, "role", Role.class, r, type);
			if (result == false) {
				con.rollback();
				return false;
			}
			String func[] = functions.split(",");
			for (String f : func) {
				if (f.length() > 0) {
					RolePriviledge role_priv = new RolePriviledge();
					role_priv.setRoleid(roleid);
					role_priv.setMenuid(Integer.valueOf(f));
					if (!insertRecode(st, null, "role_priviledge",RolePriviledge.class, role_priv, type))
						return false;
				}
			}
			
			for (String oper : opers.split(",")) {
				if (oper.length() > 0) {
					RoleOperPriviledge vo = new RoleOperPriviledge();
					vo.setRoleid(roleid);
					vo.setOperid(Integer.valueOf(oper));
					if (!insertRecode(st, null, "role_oper_priviledge",RoleOperPriviledge.class, vo, type)){
						return false;
					}
				}
			}
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	public boolean saveUser(User user, String roleIds, String functions,String opers, int type) {
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			String sql = "select max(id) from user";
			rs = st.executeQuery(sql);
			long roleid = 0;
			if (rs.next()){
				roleid = rs.getLong(1) + 1;
			}
			user.setId(roleid);
			user.setMd5(MD5.GetMD5Code(user.getUsername()));
			insertRecode(st, null, "user", User.class, user, type);
			String[] rid = roleIds.split(",");
			for (String s : rid) {
				if (s.length() == 0)
					continue;
				UserRole userrole = new UserRole();
				userrole.setRoleid(Integer.valueOf(s));
				userrole.setUserid(roleid);
				insertRecode(st, null, "user_role", UserRole.class, userrole,type);
			}
			for (String s : functions.split(",")) {
				if (s.length() > 0) {
					UserPriviledge user_priv = new UserPriviledge();
					user_priv.setUserid(roleid);
					user_priv.setMenuid(Integer.valueOf(s));
					if (!insertRecode(st, null, "user_priviledge",UserPriviledge.class, user_priv, type))
						return false;
				}
			}
			
			for (String s : opers.split(",")) {
				if (s.length() > 0) {
					UserOperPriviledge vo = new UserOperPriviledge();
					vo.setUserid(roleid);
					vo.setOperid(Integer.valueOf(s));
					if (!insertRecode(st, null, "user_oper_priviledge",UserOperPriviledge.class, vo, type))
						return false;
				}
			}
			
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	public boolean updateUserRole(User user, String roleIds, String functions,String opers,int type) {
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			updateByVo(st, null, "user", User.class, user, "id=" + user.getId());
			String sql = "delete from user_role where userid=" + user.getId();
			st.execute(sql);
			sql = "delete from user_priviledge where userid=" + user.getId();
			st.execute(sql);
			sql = "delete from user_oper_priviledge where userid=" + user.getId();
			st.execute(sql);
			
			String[] rid = roleIds.split(",");
			for (String s : rid) {
				if (s.length() == 0)
					continue;
				UserRole userrole = new UserRole();
				userrole.setRoleid(Integer.valueOf(s));
				userrole.setUserid(user.getId());
				if (!insertRecode(st, null, "user_role", UserRole.class,userrole, type))
					return false;
			}
			for (String s : functions.split(",")) {
				if (s.length() > 0) {
					UserPriviledge user_priv = new UserPriviledge();
					user_priv.setUserid(user.getId());
					user_priv.setMenuid(Integer.valueOf(s));
					if (!insertRecode(st, null, "user_priviledge",UserPriviledge.class, user_priv, type))
						return false;
				}
			}
			
			for (String s : opers.split(",")) {
				if (s.length() > 0) {
					UserOperPriviledge vo = new UserOperPriviledge();
					vo.setUserid(user.getId());
					vo.setOperid(Integer.valueOf(s));
					if (!insertRecode(st, null, "user_oper_priviledge",UserOperPriviledge.class, vo, type))
						return false;
				}
			}
			
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	public boolean updateRoleFunction(Role r, String functions, String opers ,int type) {
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			updateByVo(st, null, "role", Role.class, r, "id=" + r.getId());
			String sql = "delete from role_priviledge where roleid="+ r.getId();
			st.execute(sql);
			
			sql = "delete from role_oper_priviledge where roleid="+ r.getId();
			st.execute(sql);
			
			String func[] = functions.split(",");
			long roleid = r.getId();
			for (String f : func) {
				if (f.length() > 0) {
					RolePriviledge role_priv = new RolePriviledge();
					role_priv.setRoleid((int) roleid);
					role_priv.setMenuid(Integer.valueOf(f));
					if (!insertRecode(st, null, "role_priviledge",RolePriviledge.class, role_priv, type)){
						return false;
					}
				}
			}
			
			for (String oper : opers.split(",")) {
				if (oper.length() > 0) {
					RoleOperPriviledge vo = new RoleOperPriviledge();
					vo.setRoleid((int) roleid);
					vo.setOperid(Integer.valueOf(oper));
					if (!insertRecode(st, null, "role_oper_priviledge",RoleOperPriviledge.class, vo, type)){
						return false;
					}
				}
			}
			
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	public <T> boolean update(List<String> cols, String table, Class<T> clazz,
			T vo, String condition, int type) {
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			updateByVo(st, null, table, clazz, vo, condition);
			con.commit();
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	// 获得数据库名或表名列表
	public List<String> getNames(String hostname, String dbname) {
		List<String> nameList = new ArrayList<String>();
		DBHostPwd host = null;
		String sql = "select * from db_host where hostname = '" + hostname
				+ "'";
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			List<DBHostPwd> hosts = getAllColsByVo(sql, DBHostPwd.class,
					DBConfig.db_68_bi_sys.getIntType());
			if (hosts == null || hosts.isEmpty())
				return null;
			else
				host = hosts.get(0);
			StringBuffer url = new StringBuffer("jdbc:mysql://");
			url.append(host.getHostname() + ":");
			url.append(host.getPort());
			url.append("/mysql");
			url.append("?useUnicode=true&characterEncoding=UTF-8");
			con = DriverManager.getConnection(url.toString(),
					host.getUsername(), host.getPassword());
			st = con.createStatement();
			String command = null;
			boolean isDB = (dbname == null || "".equals(dbname));
			if (isDB) {
				command = "show databases";
			} else {
				st.execute("use `" + dbname + "`");
				command = "show tables";
			}
			rs = st.executeQuery(command);
			while (rs.next()) {
				String name = rs.getString(1);
				if (isDB && excludeDBSet.contains(name))
					continue;
				nameList.add(name);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return nameList;
	}

	// 获取某用户在某张表的权限
	public DBPriviledge getDBPri(String username, String hostname,
			String database, String tablename) throws Exception {
		StringBuffer sql = new StringBuffer(
				"select * from db_priviledge where username='");
		sql.append(username + "' and hostname='");
		sql.append(hostname + "' and dbname='");
		sql.append(database + "' and tablename='");
		sql.append(tablename + "'");
		List<DBPriviledge> priviledges = getAllColsByVo(sql.toString(),
				DBPriviledge.class, DBConfig.db_68_bi_sys.getIntType());
		if (priviledges == null || priviledges.isEmpty())
			return null;
		else
			return priviledges.get(0);
	}

	// 保存数据库权限
	public boolean saveDBPriv(String username, String password,
			List<DBHostPwd> hosts, String dbname, String tableNames,
			String priviledges) {
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Connection hostCon = null; // 到特定主机的连接
		Statement hostSt = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			String command = null;
			for (DBHostPwd host : hosts) {
				hostCon = getHostConnection(host);
				hostCon.setAutoCommit(false);
				hostSt = hostCon.createStatement();
				List<String> dbList = new ArrayList<String>();
				if ("*".equals(dbname))
					dbList = getNames(host.getHostname(), "");
				else
					dbList.add(dbname);

				for (String dbnameTemp : dbList) {
					List<String> tablesList = null;
					if ("*".equals(tableNames)) {
						tablesList = getNames(host.getHostname(), dbnameTemp);
					} else {
						String tables[] = tableNames.split(",");
						tablesList = Arrays.asList(tables);
					}
					for (String tablename : tablesList) {
						tablename = tablename.trim();
						if (tablename.length() == 0)
							continue;
						String totalPriv = "";
						DBPriviledge oldDBPriv = getDBPri(username,
								host.getHostname(), dbnameTemp, tablename);
						if (oldDBPriv != null) // 是否存在部分权限
							totalPriv = oldDBPriv.getPriviledge();
						totalPriv = priviledges + "," + totalPriv;
						String[] privs = totalPriv.split(",");
						totalPriv = "";
						HashSet<String> set = new HashSet<String>();
						for (int i = 0; i < privs.length; i++) {
							String s = privs[i];
							if ("".equals(s) || set.contains(s))
								continue;
							set.add(s);
							totalPriv += "," + s;
						}
						totalPriv = totalPriv.substring(1);
						// System.out.println(tablename+" total priv: "+totalPriv);
						DBPriviledge dbPriv = new DBPriviledge(username,
								host.getHostname(), dbnameTemp, tablename,
								totalPriv);
						StringBuffer sql = new StringBuffer(
								"delete from db_priviledge where username='");
						sql.append(username + "' and hostname='");
						sql.append(host.getHostname() + "' and dbname='");
						sql.append(dbnameTemp + "' and tablename='");
						sql.append(tablename + "'");
						st.execute(sql.toString()); // 删除旧权限
						// 将新旧权限合并，插入新权限。
						// System.out.println(sql);
						boolean result = insertRecode(st, null,
								"db_priviledge", DBPriviledge.class, dbPriv,
								DBConfig.db_68_bi_sys.getIntType());
						command = "grant " + totalPriv + " on " + dbnameTemp
								+ "." + tablename + " to '" + username
								+ "'@'%' identified by '" + password + "'";
						List<String> cmds = new ArrayList<String>();
						cmds.add(command);
						// System.out.println(command+"\n");

						boolean hostResult = grantPriv(hostSt, cmds);
						if (!(result && hostResult)) {
							throw new SQLException(
									" grant privlidge execute failed!");
						}
					}
				}
				hostCon.commit();
				con.commit();
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				hostCon.rollback();
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	// 更新数据库权限
	public boolean updateDBPriv(String username, String password,
			DBHostPwd host, String dbname, String tableName, String priviledges) {
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = null;
		Connection hostCon = null;
		Statement hostSt = null;
		ResultSet rs = null;
		try {
			hostCon = getHostConnection(host);
			con.setAutoCommit(false);
			hostCon.setAutoCommit(false);
			st = con.createStatement();
			hostSt = hostCon.createStatement();
			DBPriviledge oldDBPriv = getDBPri(username, host.getHostname(),
					dbname, tableName);
			DBPriviledge dbPriv = new DBPriviledge(username,
					host.getHostname(), dbname, tableName, priviledges);
			StringBuffer sql = new StringBuffer(
					"delete from db_priviledge where username='");
			sql.append(username + "' and hostname='");
			sql.append(host.getHostname() + "' and dbname='");
			sql.append(dbname + "' and tablename='");
			sql.append(tableName + "'");
			st.execute(sql.toString()); // 删除旧权限插入新权限。

			// 回收旧权限插入新权限。
			String revokeCmd = "revoke " + oldDBPriv.getPriviledge() + " on "
					+ dbname + "." + tableName + " from '" + username + "'";
			boolean result = true;
			boolean hostResult = true;
			List<String> cmds = new ArrayList<String>();
			cmds.add(revokeCmd);
			if (priviledges != null && !"".equals(priviledges)) {
				String grantCmd = "grant " + priviledges + " on " + dbname
						+ "." + tableName + " to '" + username
						+ "'@'%' identified by '" + password + "'";
				cmds.add(grantCmd);
				result = insertRecode(st, null, "db_priviledge",
						DBPriviledge.class, dbPriv, DBConfig.db_68_bi_sys.getIntType());
				if (!result)
					throw new SQLException("insert date to mysql failed!");
			}
			// System.out.println(cmds);
			hostResult = grantPriv(hostSt, cmds);
			if (hostResult) {
				hostCon.commit();
				con.commit();
			} else {
				throw new SQLException(cmds + " Execute failed!");
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				hostCon.rollback();
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	// 删除数据库权限
	public boolean delDBPriv(DBHostPwd host, String sqlcmd, String revokeCmd) {
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = null;
		ResultSet rs = null;
		Connection hostCon = null;
		Statement hostSt = null;
		try {
			hostCon = getHostConnection(host);
			con.setAutoCommit(false);
			hostCon.setAutoCommit(false);
			st = con.createStatement();
			hostSt = hostCon.createStatement();
			st.execute(sqlcmd); // 删除旧权限。
			// 回收旧权限。
			List<String> cmds = new ArrayList<String>();
			cmds.add(revokeCmd);
			boolean hostResult = true;
			hostResult = grantPriv(hostSt, cmds);
			if (hostResult) {
				hostCon.commit();
				con.commit();
			} else {
				throw new SQLException(cmds + "cmds Execute failed!");
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				hostCon.rollback();
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	// 删除数据库权限
	public boolean delDBPriv(String username, List<DBHostPwd> hosts,
			String dbname, String tableNames, String priviledges) {
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Connection hostCon = null; // 到特定主机的连接
		Statement hostSt = null;
		Statement st = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();

			for (DBHostPwd host : hosts) {
				hostCon = getHostConnection(host);
				hostCon.setAutoCommit(false);
				hostSt = hostCon.createStatement();
				List<String> dbList = new ArrayList<String>();
				if ("*".equals(dbname))
					dbList = getNames(host.getHostname(), "");
				else
					dbList.add(dbname);

				for (String dbnameTemp : dbList) {
					List<String> tablesList = null;
					if ("*".equals(tableNames)) {
						tablesList = getNames(host.getHostname(), dbnameTemp);
					} else {
						String tables[] = tableNames.split(",");
						tablesList = Arrays.asList(tables);
					}
					for (String tablename : tablesList) {
						tablename = tablename.trim();
						if (tablename.length() == 0)
							continue;
						DBPriviledge oldDBPriv = getDBPri(username,
								host.getHostname(), dbnameTemp, tablename);
						if (oldDBPriv == null)
							continue;
						// 是否存在部分权限
						String oldPriv = oldDBPriv.getPriviledge();
						List<String> toDelPriv = Arrays.asList(priviledges
								.split(","));
						String totalPriv = "";
						for (String priv : oldPriv.split(",")) {
							if (!toDelPriv.contains(priv)) // 如果不是要删除的权限
								totalPriv += "," + priv;
						}
						StringBuffer sql = new StringBuffer(
								"delete from db_priviledge where username='");
						sql.append(username + "' and hostname='");
						sql.append(host.getHostname() + "' and dbname='");
						sql.append(dbnameTemp + "' and tablename='");
						sql.append(tablename + "'");
						st.execute(sql.toString()); // 删除旧权限
						// 将新旧权限合并，插入新权限。
						boolean result = true;
						if (totalPriv.length() > 0) {
							totalPriv = totalPriv.substring(1);
							DBPriviledge dbPriv = new DBPriviledge(username,
									host.getHostname(), dbnameTemp, tablename,
									totalPriv);
							result = insertRecode(st, null, "db_priviledge",
									DBPriviledge.class, dbPriv,
									DBConfig.db_68_bi_sys.getIntType());
						}
						List<String> cmds = new ArrayList<String>();
						String command = "revoke " + priviledges + " on "
								+ dbnameTemp + "." + tablename + " from '"
								+ username + "'";
						cmds.add(command);
						// System.out.println(cmds);
						boolean hostResult = grantPriv(hostSt, cmds);
						// boolean hostResult=true;
						if (!(result && hostResult)) {
							throw new SQLException(
									" grant privlidge execute failed!");
						}
					}
				}
				hostCon.commit();
				con.commit();
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				hostCon.rollback();
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally {
			JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return true;
	}

	// 获得到特定主机的连接
	public Connection getHostConnection(DBHostPwd host) {
		Connection con = null;
		try {
			if (host == null)
				return null;
			StringBuffer url = new StringBuffer("jdbc:mysql://");
			url.append(host.getHostname() + ":");
			url.append(host.getPort());
			url.append("/mysql");
			url.append("?useUnicode=true&characterEncoding=UTF-8");
			con = DriverManager.getConnection(url.toString(),
					host.getUsername(), host.getPassword());
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return con;
	}

	public boolean grantPriv(Statement st, List<String> cmds) {
		try {
			for (String sqlcmd : cmds)
				st.execute(sqlcmd);
			st.execute("flush privileges");
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
